package com.zhucai.credit.repository;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.stereotype.Repository;

import com.zhucai.credit.model.PaymentOrderEntity;

/**
 * 
 * @author hsg
 * PaymentOrder
 */
@Repository
public interface PaymentOrderRepository extends
		PagingAndSortingRepository<PaymentOrderEntity, Long>,
		JpaSpecificationExecutor<PaymentOrderEntity> {


	 @Query(value = "select count(*) from tb_xa_mission_list txm where txm.contract_id=?1 and txm.contract_type=?2 and txm.status<>0 and txm.status<>6",nativeQuery = true)
    public Integer findMissionCountByUnfinished(Integer integer, Integer contractType);

	 /**
	  * 根据服务模板和服务编号查询订单信息
	  * @param delete
	  * @param string
	  * @param recordNo
	  * @return
	  */
	@Query(value="select * from finance_pay_order where status != ?1 and service_no = ?2 and order_no = ?3",nativeQuery=true)
	public PaymentOrderEntity findPaymentOrderByOrderNo(int delete,
			String string, String recordNo);

	/**
	 * 根据tid查询订单信息
	 * @param delete
	 * @param tid
	 * @return
	 */
	public PaymentOrderEntity findByStatusNotAndTid(int delete, Long tid);

	/**
	 * 支付成功更新状态
	 * @date 2018-04-27
	 * @param serviceId
	 * @param OrderId
	 * @param payMethodName
	 * @param channelSerialNum
	 * @param finishTime
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value="update finance_pay_order set pay_status ='02', pay_type = ?3, flow_number= ?4, pay_time = ?5, version = version + 1 where service_no = ?1 and order_no = ?2 and pay_status = '01' or pay_status='05'", nativeQuery=true)
	public Integer updatePaymentOrderPaySuccess(String serviceId,String OrderId,String payMethodCode,String channelSerialNum, Date finishTime);

	@Query(value = "select count(1) from  finance_pay_order po " +
			"left join finance_cr_record fcr on fcr.record_no = po.order_no " +
			"left join finance_pay_order_item fpoi on fpoi.order_no = po.order_no " +
			"where 1=1 " +
			"and (?1 is null or po.service_no = 'FINANCE_CREDIT') " +
			"and (?2 is null or fpoi.order_no like concat('%',?2,'%') or fpoi.flow_number like concat('%',?2,'%')) " +
			"and (?3 is null or po.pay_enterprise_name like concat('%',?3,'%')) " +
			"and (?4 is null or fpoi.pay_time >= ?4) " +
			"and (?5 is null or fpoi.pay_time <= ?5) " +
			"and (po.pay_status in (?6)) " +
			"and (fpoi.pay_type in (?7)) " +
			"and (fpoi.create_user in (?8)) "+ 
			"order by fpoi.create_time desc",nativeQuery = true)
	public Integer findCountPayRecord(String serviceNo, String orderNo,
			String payEnterpriseName, Date paymentStartTime,
			Date paymentEndTime, List<String> productName,
			List<String> payTypeList, List<Long> purUserList);

	@Query(value = "select po.id,po.service_no,fpoi.order_no,po.pay_enterprise_name,po.pay_enterprise_id,"
			+ " po.collect_enterprise_name,po.order_amt,po.product_name,"
			+ " fpoi.pay_amt,fpoi.pay_time,fpoi.pay_status,fpoi.pay_type," 
			+ " fpoi.flow_number,fpoi.trade_status,fpoi.id itemId from  finance_pay_order po "+
			"left join finance_cr_record fcr on fcr.record_no = po.order_no " +
			"left join finance_pay_order_item fpoi on fpoi.order_no = po.order_no " +
			"where 1=1 " +
			"and (?1 is null or po.service_no = 'FINANCE_CREDIT') " +
			"and (?2 is null or fpoi.order_no like concat('%',?2,'%') or fpoi.flow_number like concat('%',?2,'%')) " +
			"and (?3 is null or po.pay_enterprise_name like concat('%',?3,'%')) " +
			"and (?4 is null or fpoi.pay_time >= ?4) " +
			"and (?5 is null or fpoi.pay_time <= ?5) " +
			"and (fpoi.pay_status in (?6)) " +
			"and (fpoi.pay_type in (?7)) "+
			"and (fpoi.create_user in (?8)) "+ 
			"order by fpoi.create_time desc limit ?9,?10",nativeQuery = true)
	public List<Object[]> findPayRecordList(String emptyToNull,
			String emptyToNull2, String emptyToNull3,
			Date paymentStartTime, Date paymentEndTime, List<String> emptyToNull4,
			List<String> emptyToNull6, List<Long> purList, int i, Integer pageSize);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value="update finance_pay_order set trade_status =?2 where id = ?1 and trade_status = 'NORMAL'", nativeQuery=true)
	public void updateTradeStatusById(Long tid, String string);

	/**
	 * 根据订单编号查询已支付完成且融资单状态为未支付完全的数据
	 * @param status
	 * @param payStatus
	 * @return
	 */
	@Query(value="select pay.* from finance_pay_order pay inner join finance_cr_record record on "
			+ "record.record_no = pay.order_no where pay.status != ?1 and pay.pay_status = ?2 and record.credit_status in ('WAIT_PAY') ", nativeQuery=true)
	public List<PaymentOrderEntity> findPayOrderForInfo(int status,String payStatus);

	/**
	 * 线下支付：支付已确认，更新订单状态
	 * @param serviceId
	 * @param orderId
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value="update finance_pay_order set pay_status = '05',pay_Type='OFFLINE_PAYMENT', version = version + 1 where service_no = ?1 and order_no = ?2 and pay_status = '01'", nativeQuery=true)
	public Integer updatePaymentOrderPaySuccess(String serviceId, String orderId);

	/**
	 * 根据订单明细id 查询订单信息
	 * @param delete
	 * @param id
	 * @return
	 */
	@Query(value = "select po.id,po.service_no,fpoi.order_no,po.pay_enterprise_name,po.pay_enterprise_id,"
			+ " po.collect_enterprise_name,po.order_amt,po.product_name,"
			+ " fpoi.pay_amt,fpoi.pay_time,fpoi.pay_status,fpoi.pay_type," 
			+ " fpoi.flow_number,fpoi.trade_status,fpoi.id itemId from  finance_pay_order po "+
			"left join finance_pay_order_item fpoi on fpoi.order_no = po.order_no " +
			"where fpoi.status<>?1 "
			+" and fpoi.id=?2"
			,nativeQuery = true)
	public List<Object[]> findByItemId(int delete, Long id);

	/**
	 * 根据订单编号查询订单信息
	 * @param delete
	 * @param orderNo
	 * @return
	 */
	public PaymentOrderEntity findByStatusNotAndOrderNo(int delete,
			String orderNo);

	@Query(value = "select * from finance_pay_order where status != ?1 and service_no = ?2 and order_no in (?3)",nativeQuery = true)
	public List<PaymentOrderEntity> findPaymentOrderByOrderNoList(int delete,
			String string, List<String> recordNoList);

	/**
	 * 根据融资单号查询订单信息
	 * @param delete
	 * @param string
	 * @param record
	 * @return
	 */
	@Query(value = "select fpo.create_user,fpo.collect_enterprise_name,fpo.create_order_time,fpo.discount_amt,fpo.flow_number," +
			" fpo.order_amt,fpo.order_no,fpo.pay_enterprise_id,txs.enterprise_name,fpoi.pay_status,fpo.pay_time,fpo.pay_type,fpo.product_name," +
			" fpo.product_remark,fpo.service_no,fpo.trade_status,fpt.cr_prod_bank_name " +
			" from finance_pay_order fpo" +
			" left join finance_cr_record fcr on fpo.order_no = fcr.record_no" +
			" left join finance_product_type fpt on fpt.id = fcr.cr_prod_id" +
			" left join tb_xa_supplier txs on txs.id = fcr.participator_enterprise_id" +
			" left join finance_pay_order_item fpoi on fpoi.order_no = fpo.order_no" +
			" where 1=1" +
			" and fpo.status <>?1" +
			" and fpo.service_no = ?2" +
			" and fpo.order_no = ?3",nativeQuery = true)
	public List<Object[]> findAllByOrderNo(int delete, String string,
			String record);
	@Query(value = "select fpo.create_user,fpo.collect_enterprise_name,fpo.create_order_time,fpo.discount_amt,fpo.flow_number," +
			" fpo.order_amt,fpo.order_no,fpo.pay_enterprise_id,txp.enterprise_name,fpoi.pay_status,fpo.pay_time,fpo.pay_type,fpo.product_name," +
			" fpo.product_remark,fpo.service_no,fpo.trade_status,fpt.cr_prod_bank_name " +
			" from finance_pay_order fpo" +
			" left join finance_cr_record fcr on fpo.order_no = fcr.record_no" +
			" left join finance_product_type fpt on fpt.id = fcr.cr_prod_id" +
			" left join tb_xa_purchaser txp on txp.id = fcr.originator_enterprise_id" +
			" left join finance_pay_order_item fpoi on fpoi.order_no = fpo.order_no" +
			" where 1=1" +
			" and fpo.status <>?1" +
			" and fpo.service_no = ?2" +
			" and fpo.order_no = ?3",nativeQuery = true)
	public List<Object[]> findAllByOrderNo1(int delete, String string,
			String record);

	/**
	 * 更新支付主单的订单金额
	 * @param orderAmt
	 * @param orderAmt2
	 * @param orderNo
	 * @param i
	 * @return
	 */
	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value= "update finance_pay_order set order_amt = ?1,discount_amt = ?2 where order_no = ?3 and status <>?4",nativeQuery = true)
	public int updateOrderAmtByOrderNo(BigDecimal orderAmt,
			BigDecimal orderAmt2, String orderNo, int i);

	/**
	 * 根据融资单号查询订单信息
	 * @param orderNo
	 * @param i
	 * @return
	 */
	@Query(value = "select * from finance_pay_order where order_no = ?1 and status <>?2",nativeQuery = true)
	public PaymentOrderEntity findByOrderNoAndStatusNot(String orderNo, int i);

	@Transactional
	@Modifying(clearAutomatically = true)
	@Query(value = "update finance_pay_order set pay_status = '02' where id = ?1",nativeQuery = true)
	public void updatePayStatusById(Long tid);




	





}
